package com.fare.taluo.queue;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.annotation.Resource;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import com.dayatang.domain.Entity;
import com.dayatang.domain.InstanceFactory;
import com.fare.taluo.application.FreightPriceApplication;
import com.fare.taluo.application.impl.FreightPriceApplicationImpl;
import com.fare.taluo.application.report.careport.ReportStaticData;
import com.fare.taluo.application.system.email.EmailSendApplication;
import com.fare.taluo.application.system.email.ReportEmailApplication;
import com.fare.taluo.core.domain.BaseEntity;
import com.fare.taluo.core.domain.queue.message.QueueEmailMessageEntity;
import com.fare.taluo.util.DateUtils;
import com.fare.taluo.vo.core.PlaneInfo;
import com.fare.taluo.vo.core.ReturnTripEntityVO;
import com.fare.taluo.vo.reportemail.EmailVO;
import com.fare.util.ReportProperties;
@Service
public class ExportOtaTemplateQuartzJob extends BaseEntity<ExportOtaTemplateQuartzJob> implements Entity{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String date_sheet1_7;
	private String date_sheet1_30;
	private String date_sheet1_45;
	private String date_sheet1_60;
	private String date_sheet1_90;
	private String date_sheet2_7;
	private String date_sheet2_14;
	private String date_sheet2_30;
	private String currentDate;
	private List<String> sheet1List = ReportStaticData.initSheet1();
	private List<String> sheet2List = ReportStaticData.initSheet2();
	private List<String> sheet1ListDate = new ArrayList<String>();
	private List<String> sheet2ListDate = new ArrayList<String>();
	private String[] sheet1HbaseKeys = { "20006", "00003", "00004"};//官网20006，携程00003，艺龙00004，一起飞00005 cc暂时为00000
	private String[] sheet2HbaseKeys = { "20006", "00003" };
	private jxl.write.WritableFont titleFont;
	private jxl.write.WritableCellFormat titleFormat;
	private jxl.write.WritableFont normalFont;
	private jxl.write.WritableFont boldFont;
	private jxl.write.WritableCellFormat normalFormat;
	private jxl.write.WritableCellFormat rangeFormat;
	private jxl.write.WritableCellFormat boldFormat;
	private jxl.write.WritableCellFormat redFormat;
	//保存第一个表信息
	private Map<String, List<Map<String,PlaneInfo>>> sheet1Map = null;
	//保存第二个表信息
	private Map<String, List<Map<String,PlaneInfo>>> sheet2Map = null;
	private String dateRangeFirst = null;
	private String dateRangeSecond = null;
	private String email = null;
	
	@Resource(name ="reportEmailApplication")
	private ReportEmailApplication reportEmailApplication;
	
	private List<EmailVO> emailList;
	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}
	
	public List<EmailVO> getEmailList() {
		return emailList;
	}

	public void setEmailList(List<EmailVO> emailList) {
		this.emailList = emailList;
	}

	public void execute() throws Exception {
		try {
			//获取后台要发送的Email
			if(email==null || email==""){
				emailList=reportEmailApplication.findAllEmail(1);//0表示不接受状态，1表示接受状态
				StringBuffer emailStr=new StringBuffer();
				for(EmailVO emailVo:emailList){
					emailStr.append(","+emailVo.getEmailAddress());
				}
				if(emailStr.length()>1 && emailStr !=null){
					email=emailStr.toString().substring(1);
				}
			}
			this.initDate();
			sheet1List = ReportStaticData.initSheet1();
			sheet2List = ReportStaticData.initSheet2();
			
		
			// 获得数据
			sheet1Map = this.getSheet1Map();
			sheet2Map = this.getSheet2Map();
			//生成路径
			String path = ReportProperties.getInstance().getSavePath().endsWith("/")
					? ReportProperties.getInstance().getSavePath() :
						ReportProperties.getInstance().getSavePath()+"/";
			
			String fileName = "国航国际旅行航线OTA对比官网直销报表_"+currentDate+".xls";
			//生成excel
			exportExcel(sheet1Map, sheet2Map,path,fileName);

			File file = new File(path+fileName);
			if(file.exists() && StringUtils.isNotBlank(getEmail())){
				QueueEmailMessageEntity email = new QueueEmailMessageEntity();
				email.setAttachPath(file.getPath());//new String(location.getBytes("UTF-8"),"ISO8859-1")
				email.setBody("<p>您好!<p><p>&nbsp;&nbsp;已生成您需要的国际旅行航线OTA对比官网直销报表，"+"欧美"+dateRangeFirst+"</p>"
				+"<p>东南亚"+dateRangeSecond+"</p>"+"<p>请查收附件。</p>");
				email.setSubject("国际旅行航线OTA对比官网直销报表_"+currentDate);
				email.setToAddress(getEmail());
				email.setName("国际旅行航线OTA对比官网直销报表");
				InstanceFactory.getInstance(EmailSendApplication.class).save(email);
			
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		
	}
	
	private Map<String, List<Map<String,PlaneInfo>>> getSheet1Map() throws Exception{
		Map<String, List<Map<String,PlaneInfo>>> sheet1Map = new HashMap<String, List<Map<String,PlaneInfo>>>();
			// 每行循环
				for (int i = 0; i < sheet1List.size(); i++) {
					
					String info = (String) sheet1List.get(i);
					String[] flightInfoArr = info.split("_");
					
					//String flightNo = flightInfoArr[1].split("-")[0];
					String flightNo = flightInfoArr[1];
					String fromCity = flightInfoArr[2].split("-")[0];
					String toCity = flightInfoArr[2].split("-")[1];
					// 日期循环
					List<Map<String,PlaneInfo>> listlist = new ArrayList<Map<String,PlaneInfo>>();// 保存一行
				
					for (int j = 0; j < sheet1ListDate.size(); j++) {	
						//sheet1ListDate [20140107, 20140130, 20140214, 20140301, 20140331]
						String flightDate = (String) sheet1ListDate.get(j);
						//获得返程日期+90
						String returnDate = DateUtils.getNextDateByDateStr(flightDate, 90, "yyyyMMdd");
						
						// 每个渠道循环
						Map<String,PlaneInfo> map = new HashMap<String,PlaneInfo>();// 保存一行中某天所有渠道
						for (String hbaseKey : sheet1HbaseKeys) {
							//String startRowKey = hbaseKey + fromCity + toCity+ flightDate+returnDate
							//		+ StringUtils.leftPad(flightNo, 8, "0")  + "0000000000000000";
							//String endRowKey = hbaseKey + fromCity + toCity+ flightDate+returnDate
							//		+ StringUtils.leftPad(flightNo, 8, "0")  + "ZZZZZZZZZZZZZZZZ";
																		    
							FreightPriceApplication application = new FreightPriceApplicationImpl();
							//查询hbase暂时注释
							/*PlaneInfo plane = application.findInfoToExcel("LP_FLIGHT_DOUBLE",
									startRowKey, endRowKey,currentDate,null, null);
							if(plane != null){
								String returnFlightNo = getReturnFlightNo(plane);
								
								CabinRelationEntityVO fullPrice = plane.getLowestFullPrice(returnFlightNo);
								if(fullPrice != null){
									plane.setRoundTripPrice(fullPrice.getFullPrice());
									map.put(hbaseKey, plane);
								}else {
									map.put(hbaseKey, null);
								}
							}*/
							
							/**
							 * 查询mysql
							 */
							PlaneInfo plane = application.findInfoToExcel(hbaseKey, fromCity, toCity, flightDate, "CA", com.fare.util.DateUtils.getDateStr("yyyy-MM-dd"), flightNo);
							if(plane != null){
								map.put(hbaseKey, plane);
							}
							
						}
						listlist.add(map);	
					}
					sheet1Map.put(info, listlist);//
				}
				return sheet1Map;
	}
	
	private String getReturnFlightNo(PlaneInfo plane){
		if(null != plane){
			String flightNo = plane.getFlightId();//去程航班号
			int flightNoNum = Integer.parseInt(flightNo.substring(2));//ca981
			flightNoNum += 1;
			return flightNo.substring(0,2)+flightNoNum;	
		}else {
			return null;
		}
	
	}
	
	private PlaneInfo getRealRoundTripPrice(PlaneInfo plane){
		if(null != plane){
		
			String flightNo = plane.getFlightId();//去程航班号
			int flightNoNum = Integer.parseInt(flightNo.substring(2));//ca981
			List<ReturnTripEntityVO> returnTripVOList = plane.getReturnTrip();//返程航班集
			for (ReturnTripEntityVO returnTripEntityVO : returnTripVOList) {
				if(returnTripEntityVO.getFlightNo()!=null){
					String returnFlightNo = returnTripEntityVO.getFlightNo();
					int returnNum = Integer.parseInt(returnFlightNo.substring(2));
					//ca981、ca982
					if(returnNum - flightNoNum == 1){
						if(null == returnTripEntityVO.getTotalPrice()){
							return null;
						}else{
							plane.setRoundTripPrice(""+returnTripEntityVO.getTotalPrice());
						}
						
					}
				}
				
			}
			System.out.println(plane.getGrabChannelNum()+"-"+plane.getFlightId()+"-"+plane.getRoundTripPrice());
			if(null == plane.getRoundTripPrice()){
				return null;
			}else{
				return plane;
			}
			
		}	
		return null;
	}
	
	private Map<String, List<Map<String,PlaneInfo>>> getSheet2Map() throws Exception{
		Map<String, List<Map<String,PlaneInfo>>> sheet2Map = new HashMap<String, List<Map<String,PlaneInfo>>>();
		// 每行循环
		for (int i = 0; i < sheet2List.size(); i++) {
					String info =  (String) sheet2List.get(i);
					String[] flightInfoArr = info.split("_");
					//String flightNo = flightInfoArr[1].split("-")[0];
					String flightNo = flightInfoArr[1];
					String fromCity = flightInfoArr[2].split("-")[0];
					String toCity = flightInfoArr[2].split("-")[1];
					// 日期循环
					List<Map<String,PlaneInfo>> listlist = new ArrayList<Map<String,PlaneInfo>>();// 保存一行
					for (int j = 0; j < sheet2ListDate.size(); j++) {					
						String flightDate = (String) sheet2ListDate.get(j);	
						//获得返程日期+15
						String returnDate = DateUtils.getNextDateByDateStr(flightDate, 15, "yyyyMMdd");
						// 每个渠道循环
						Map<String,PlaneInfo> map = new HashMap<String,PlaneInfo>();// 保存一行中某天所有渠道
						for (String hbaseKey : sheet2HbaseKeys) {
							/*String startRowKey = hbaseKey + fromCity + toCity+ flightDate+returnDate
									+ StringUtils.leftPad(flightNo, 8, "0")  + "0000000000000000";
							String endRowKey = hbaseKey + fromCity + toCity+ flightDate+returnDate
									+ StringUtils.leftPad(flightNo, 8, "0")  + "ZZZZZZZZZZZZZZZZ";
							FreightPriceApplication application = new FreightPriceApplicationImpl();
							PlaneInfo plane = application.findInfoToExcel("LP_FLIGHT_DOUBLE",
									startRowKey, endRowKey,currentDate, null,null);
							if(null != plane){
								String returnFlightNo = getReturnFlightNo(plane);
								CabinRelationEntityVO fullPrice = plane.getLowestFullPrice(returnFlightNo);
								plane.setRoundTripPrice(fullPrice != null ? fullPrice.getFullPrice() : null);
								map.put(hbaseKey, plane);
							}else {
								map.put(hbaseKey, null);
							}*/
							FreightPriceApplication application = new FreightPriceApplicationImpl();
							PlaneInfo plane = application.findInfoToExcel(hbaseKey, fromCity, toCity, flightDate, "CA", com.fare.util.DateUtils.getDateStr("yyyy-MM-dd"), flightNo);
							if(plane != null){
								map.put(hbaseKey, plane);
							}
						}
						listlist.add(map);
					}
					sheet2Map.put(info, listlist);
				}
				return sheet2Map;
	}
	
	private void exportExcel(Map<String, List<Map<String,PlaneInfo>>> sheet1Map,
			Map<String, List<Map<String,PlaneInfo>>> sheet2Map,String path,String fileName) throws Exception {
		WritableWorkbook wwb = null;
		OutputStream os = null;
		File file = null;
		try{
			this.initDate();
			file = this.createFile(path, fileName);
			os=new FileOutputStream(file);
			wwb = Workbook.createWorkbook(os); // 建立excel文件
			// 定义样式
			jxl.write.WritableFont normalFont = new jxl.write.WritableFont(
					WritableFont.createFont("宋体"), 10);
			jxl.write.WritableCellFormat normalFormat = new jxl.write.WritableCellFormat(
					normalFont);
			normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
			normalFormat.setAlignment(Alignment.CENTRE);
			normalFormat.setWrap(false);
	
			WritableSheet ws = wwb.createSheet("欧美", 0); // sheet名称
			ws = setHeadFirst(ws);
			ws = setDataFirst(ws,sheet1Map);
	
			ws = wwb.createSheet("东南亚", 1); // sheet名称
			ws = setHeadSecond(ws);
			ws = setDataSecond(ws,sheet2Map);
			wwb.write();
		}finally{
			
			if(wwb != null) wwb.close();
			if(os != null) os.close();
			wwb = null;
			os = null;
			file = null;
		}
	}

	private WritableSheet setDataFirst(WritableSheet ws,Map<String, List<Map<String,PlaneInfo>>> sheet1Map) throws Exception {
		int row = 10;
		int rowIndex = 3 ;
		this.formatWriteable();
		TreeMap<String,List<Map<String,PlaneInfo>>> treeMap = new TreeMap<String,List<Map<String,PlaneInfo>>>(sheet1Map);
		 dateRangeFirst = "";
		Label dateRangeLabel = new Label(0, 0, dateRangeFirst, rangeFormat);
		ws.addCell(dateRangeLabel);
		Iterator keys = treeMap.keySet().iterator();
		int index = 3;
		while(keys.hasNext()){ //代表行数
			String key =(String) keys.next();//key
			List<Map<String,PlaneInfo>> listlist = (List<Map<String,PlaneInfo>>)sheet1Map.get(key);//上面key对应的value

			String[] infoArr = key.split("_");
			String flightNo = infoArr[1];
			String cityEnglish = infoArr[2];
			String cityChinese = infoArr[0]; 
			Label flightNoLabel0 = new Label(0, index, cityChinese, normalFormat);
			ws.addCell(flightNoLabel0);
			Label flightNoLabel1 = new Label(1, index, cityEnglish, normalFormat);
			ws.addCell(flightNoLabel1);
			Label flightNoLabel2 = new Label(2, index, flightNo, normalFormat);
			ws.addCell(flightNoLabel2);
			
			//填充每天信息
			int columnIndex = 3;
			int temp = 8;
			
			for (int i = 0; i < listlist.size(); i++) { //代表列数
				
				Map<String,PlaneInfo> infoMap = listlist.get(i);
				for (; columnIndex < temp; ) {
					//官网20006，携程00003，艺龙00004，一起飞00005  cc暂时为00000
					//System.out.println(String.valueOf(infoMap.get("20006").getRoundTripPrice()).split("\\.").length);
					/*Integer B2C = Integer.parseInt(String.valueOf(infoMap.get("20006").getRoundTripPrice()).split("\\.")[0]);
					Integer CC = Integer.parseInt(String.valueOf(infoMap.get("00000").getRoundTripPrice()).split("\\.")[0]); 
					Integer xiecheng = Integer.parseInt(String.valueOf(infoMap.get("00003").getRoundTripPrice()).split("\\.")[0]) ;
					Integer yilong =Integer.parseInt(String.valueOf(infoMap.get("00004").getRoundTripPrice()).split("\\.")[0]); 
					Integer yiqifei =Integer.parseInt(String.valueOf(infoMap.get("00005").getRoundTripPrice()).split("\\.")[0]); */
					
					double B2C = Double.parseDouble(infoMap.get("20006")==null || infoMap.get("20006").getRoundTripPrice()==null ?"0.0":infoMap.get("20006").getRoundTripPrice());
				//	double CC = Double.parseDouble(infoMap.get("00000")==null || infoMap.get("00000").getRoundTripPrice()==null?"0.0":infoMap.get("00000").getRoundTripPrice()); 
					double xiecheng = Double.parseDouble(infoMap.get("00003")==null || infoMap.get("00003").getRoundTripPrice()==null?"0.0":infoMap.get("00003").getRoundTripPrice());
					double yilong = Double.parseDouble(infoMap.get("00004")==null || infoMap.get("00004").getRoundTripPrice()==null?"0.0":infoMap.get("00004").getRoundTripPrice()); 
				//	double yiqifei = Double.parseDouble(infoMap.get("00005")==null || infoMap.get("00005").getRoundTripPrice()==null?"0.0":infoMap.get("00005").getRoundTripPrice()); 
					
					
					Label B2CLabel = new Label(columnIndex, rowIndex,B2C==0.0 ?"-":(""+B2C).split("\\.")[0], boldFormat);
					ws.addCell(B2CLabel);	
//					//CC的值还没有取到，输出/
//					Label CCLabel = new Label(++columnIndex, rowIndex, "-", normalFormat);
//					ws.addCell(CCLabel);
//					
//					//CC的值还没有取到，输出/
//					Label CC_B2C = new Label(++columnIndex, rowIndex, "-", normalFormat);
//					ws.addCell(CC_B2C);
					Label yilongLabel = new Label(++columnIndex, rowIndex,  yilong==0.0 ?"-":(""+yilong).split("\\.")[0], boldFormat);
					ws.addCell(yilongLabel);
					
				
						if(yilong == 0.0 || B2C == 0.0){
							Label yilong_B2C = new Label(++columnIndex, rowIndex, "-", normalFormat);
							ws.addCell(yilong_B2C);
						}else if(yilong-B2C < 0){
							Label yilong_B2C = new Label(++columnIndex, rowIndex, (""+(yilong-B2C)).split("\\.")[0], redFormat);
							ws.addCell(yilong_B2C);
						}else if(yilong-B2C > 0){
							Label yilong_B2C = new Label(++columnIndex, rowIndex, yilong-B2C==0.0 ?"-":(""+(yilong-B2C)).split("\\.")[0], normalFormat);
							ws.addCell(yilong_B2C);
						}
					
					Label xiechengLabel = new Label(++columnIndex, rowIndex, xiecheng==0.0 ?"-":(""+xiecheng).split("\\.")[0], boldFormat);
					ws.addCell(xiechengLabel);
					

					
						if(xiecheng == 0.0 || B2C == 0.0){
							Label xiecheng_B2C = new Label(++columnIndex, rowIndex, "-", normalFormat);
							ws.addCell(xiecheng_B2C);
						}else if( (xiecheng-B2C)<0){
							Label xiecheng_B2C = new Label(++columnIndex, rowIndex, (""+(xiecheng-B2C)).split("\\.")[0], redFormat);
							ws.addCell(xiecheng_B2C);
						}		
						else if( (xiecheng-B2C)>0){
						Label xiecheng_B2C = new Label(++columnIndex, rowIndex,xiecheng-B2C==0.0 ?"-":(""+(xiecheng-B2C)).split("\\.")[0], normalFormat);
						ws.addCell(xiecheng_B2C);
						}

					
					
					
//					Label yiqifeiLabel = new Label(++columnIndex, rowIndex, yiqifei==0.0 ?"-":(""+yiqifei).split("\\.")[0], boldFormat);
//					ws.addCell(yiqifeiLabel);
					
					
						
//						if(yiqifei == 0.0 || B2C == 0.0){
//							Label yiqifei_B2C = new Label(++columnIndex, rowIndex,  "-", normalFormat);
//							ws.addCell(yiqifei_B2C);
//						}else if( (yiqifei-B2C)<0){
//							Label yiqifei_B2C = new Label(++columnIndex, rowIndex,  (""+(yiqifei-B2C)).split("\\.")[0], redFormat);
//							ws.addCell(yiqifei_B2C);
//						}else if( (yiqifei-B2C)>0){
//						Label yiqifei_B2C = new Label(++columnIndex, rowIndex, yiqifei-B2C==0.0 ?"-":(""+(yiqifei-B2C)).split("\\.")[0], normalFormat);
//						ws.addCell(yiqifei_B2C);
//					}
					
					
					columnIndex++;
				}
				
				temp+=5;
	
			}		
			index++;
			rowIndex++;
		}
		
		
		
//		
//		for (int i = 0; i < sheet1List.size(); i++) {
//			String flightNo = sheet1List.get(i).split("_")[1];
//			String cityInfo = sheet1List.get(i).split("_")[0];
//			if(i!=0 && !cityInfo.equals(sheet1List.get(i-1).split("_")[0])){
//				Label flightNoLabel = new Label(1, row, "", normalFormat);
//				ws.addCell(flightNoLabel);
//				Label flightNoLabel2 = new Label(1, ++row, flightNo, normalFormat);
//				ws.addCell(flightNoLabel2);	
//				
//			}else{
//				Label flightNoLabel = new Label(1, row, flightNo, normalFormat);
//				ws.addCell(flightNoLabel);		
//				
//			}
//			row++;
//		
//			
//		}
//		
//		
//		//暂时填充空格
//		for (int i = 2; i < 48; i++) {
//			for (int j = 10; j < 19; j++) {
//				Label flightNoLabel = new Label(i, j, "", normalFormat);
//				ws.addCell(flightNoLabel);
//			}	
//		}
//		Label flightNoLabel = new Label(2, 10, "舱位组合/上座率", normalFormat);
//		ws.addCell(flightNoLabel);
		
		return ws;
	}
	private String getSheetDateRange(List<PlaneInfo> sheetDateRange){
		
		if(null != sheetDateRange && sheetDateRange.size() > 0 ){
			String rangeDateString = null;
			Collections.sort(sheetDateRange, new Comparator<PlaneInfo>(){

				@Override
				public int compare(PlaneInfo o1, PlaneInfo o2) {	
					return o1.getGrapDateStr().compareTo(o2.getGrapDateStr());
				}
				
			});
			rangeDateString = "取数时间 " + sheetDateRange.get(0).getGrapDateStr().substring(11, 16)+"-"+sheetDateRange.get(sheetDateRange.size()-1).getGrapDateStr().substring(11, 16);
			return rangeDateString;
		}else{
			return "";
		}
		
	}
	private void formatWriteable() throws WriteException {
		titleFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				10, WritableFont.BOLD);
		titleFont.setColour(Colour.WHITE);
		titleFormat = new jxl.write.WritableCellFormat(titleFont);
		titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
		titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
		titleFormat.setAlignment(Alignment.CENTRE); // 水平对齐
		titleFormat.setWrap(true); // 是否换行
		titleFormat.setBackground(Colour.OCEAN_BLUE);// 背景色暗灰-25%
		
		
		
		normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11);
		normalFormat = new jxl.write.WritableCellFormat(normalFont);
		normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat.setAlignment(Alignment.CENTRE);
		normalFormat.setWrap(false);
		
		
		normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11);
		rangeFormat = new jxl.write.WritableCellFormat(normalFont);
		rangeFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		rangeFormat.setAlignment(Alignment.CENTRE);
		rangeFormat.setWrap(false);
		
		boldFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11,WritableFont.BOLD);
		boldFormat = new jxl.write.WritableCellFormat(boldFont);
		boldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		boldFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		boldFormat.setAlignment(Alignment.CENTRE);
		boldFormat.setWrap(false);
		
		redFormat = new jxl.write.WritableCellFormat(normalFont);
		redFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		redFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		redFormat.setAlignment(Alignment.CENTRE);
		redFormat.setWrap(false);
		redFormat.setBackground(Colour.RED);
	}

	private WritableSheet setHeadSecond(WritableSheet ws) throws Exception {

		this.formatWriteable();
		Label fenfangxiangLabel = new Label(0, 1, "分方向", titleFormat);
		ws.addCell(fenfangxiangLabel);
		ws.setColumnView(0, 20);
		Label hangxianLabel = new Label(1, 1, "航线", titleFormat);
		ws.addCell(hangxianLabel);
		ws.setColumnView(1, 20);
		Label flightNoLabel = new Label(2,1, "航班号", titleFormat);
		ws.addCell(flightNoLabel);
		ws.setColumnView(2, 20);

		for (int i = 3; i < 12; i++) {

			if (i == 3) {
				Label date7Label = new Label(i, 1, "+7" + "(" +  date_sheet2_7.substring(4, 6)+"."+date_sheet2_7.substring(6, 8)
						+ ")", titleFormat);
				ws.addCell(date7Label);
			} else if (i == 6) {
				Label date7Label = new Label(i, 1, "+14" + "(" +  date_sheet2_14.substring(4, 6)+"."+date_sheet2_14.substring(6, 8)
						+ ")", titleFormat);
				ws.addCell(date7Label);
			} else if (i == 9) {
				Label date7Label = new Label(i,1, "+30" + "(" +   date_sheet2_30.substring(4, 6)+"."+date_sheet2_30.substring(6, 8)
						+ ")", titleFormat);
				ws.addCell(date7Label);
			} 
			Label b2cLabel = new Label(i, 2, "B2C", titleFormat);
			ws.addCell(b2cLabel);
			ws.setColumnView(i, 15);
//			Label ccLabel = new Label(++i, 2, "CC", titleFormat);
//			ws.addCell(ccLabel);
//			ws.setColumnView(i, 15);
//			Label sale_cc_7 = new Label(++i, 2, "直销对比", titleFormat);
//			ws.addCell(sale_cc_7);
//			ws.setColumnView(i, 15);		
			Label xiechengLabel = new Label(++i, 2, "携程", titleFormat);
			ws.addCell(xiechengLabel);
			ws.setColumnView(i, 15);
			Label sale_xiecheng_7 = new Label(++i,2, "对比直销", titleFormat);
			ws.addCell(sale_xiecheng_7);
			ws.setColumnView(i, 15);

		}
		ws.mergeCells(0,0, 1, 0);
		ws.mergeCells(0, 1, 0, 2);
		ws.mergeCells(1, 1, 1, 2);
		ws.mergeCells(2, 1, 2, 2);
		ws.mergeCells(3, 1, 5, 1);
		ws.mergeCells(6, 1, 8, 1);
		ws.mergeCells(9, 1, 11, 1);
		
		return ws;
	}

	private WritableSheet setDataSecond(WritableSheet ws,Map<String, List<Map<String,PlaneInfo>>> sheet2Map) throws Exception {
		int row = 20;
		this.formatWriteable();
		TreeMap<String,List<Map<String,PlaneInfo>>> treeMap = new TreeMap<String,List<Map<String,PlaneInfo>>>(sheet2Map);
		 dateRangeSecond = "";
		Label dateRangeLabel = new Label(0, 0, dateRangeSecond, rangeFormat);
		ws.addCell(dateRangeLabel);
		Iterator keys = treeMap.keySet().iterator();
		int index = 3;
		int rowIndex = 3 ;
		while(keys.hasNext()){
			String key =(String) keys.next();//key
			List<Map<String,PlaneInfo>> listlist = (List<Map<String,PlaneInfo>>)sheet2Map.get(key);//上面key对应的value

			//按行填充航班信息
			String[] infoArr = key.split("_");
			String flightNo = infoArr[1];
			String cityEnglish = infoArr[2];
			String cityChinese = infoArr[0]; 
			Label flightNoLabel0 = new Label(0, index, cityChinese, normalFormat);
			ws.addCell(flightNoLabel0);
			Label flightNoLabel1 = new Label(1, index, cityEnglish, normalFormat);
			ws.addCell(flightNoLabel1);
			Label flightNoLabel2 = new Label(2, index, flightNo, normalFormat);
			ws.addCell(flightNoLabel2);	
			
			
			//填充每天信息
			int  columnIndex = 3;
			int temp = 6;
			//listlist保存一行信息
			for (int i = 0; i < listlist.size(); i++) {
				//每个infoMap保存某天所有渠道信息
				Map<String,PlaneInfo> infoMap = listlist.get(i);
				for (; columnIndex < temp; ) {
					//官网20006，携程00003，艺龙00004，一起飞00005
					 
					Double B2C = Double.parseDouble(infoMap.get("20006")==null || infoMap.get("20006").getRoundTripPrice()==null ?"0.0":infoMap.get("20006").getRoundTripPrice());//infoMap.get("20006").getRoundTripPrice();infoMap.get("20006")==null?"0.0":infoMap.get("20006").getRoundTripPrice()
					Double xiecheng =  Double.parseDouble(infoMap.get("00003")==null || infoMap.get("00003").getRoundTripPrice()==null?"0.0":infoMap.get("00003").getRoundTripPrice());
					Label B2CLabel = new Label(columnIndex, rowIndex,B2C==0.0 ?"-":(""+B2C).split("\\.")[0] , boldFormat);
					ws.addCell(B2CLabel);	
					
					Label xiechengLabel = new Label(++columnIndex, rowIndex, xiecheng==0.0 ?"-":(""+xiecheng).split("\\.")[0], boldFormat);
					ws.addCell(xiechengLabel);
					
					
						
						if(xiecheng == 0.0 || B2C == 0.0){
							Label xiecheng_B2C = new Label(++columnIndex, rowIndex, "-", normalFormat);
							ws.addCell(xiecheng_B2C);	
						}else if((xiecheng-B2C)<0){
							Label xiecheng_B2C = new Label(++columnIndex, rowIndex, (""+(xiecheng-B2C)).split("\\.")[0], redFormat);
							ws.addCell(xiecheng_B2C);	
						}
						
					else if((xiecheng-B2C)>0){
						Label xiecheng_B2C = new Label(++columnIndex, rowIndex, xiecheng-B2C==0.0 ?"-":(""+(xiecheng-B2C)).split("\\.")[0], normalFormat);
						ws.addCell(xiecheng_B2C);	
					}
					columnIndex++;	
				}
				
				temp+=3;
					
			}			
			rowIndex++;					
			index++;
			
		}
		
		
		
//		for (int i = 0; i < sheet2List.size(); i++) {
//			String flightNo = sheet2List.get(i).split("_")[1];
//			String cityInfo = sheet2List.get(i).split("_")[0];
//			//使用航线一致的在一起，不一致的隔开一空行
//			if(i!=0 && !cityInfo.equals(sheet2List.get(i-1).split("_")[0])){
//				Label flightNoLabel = new Label(1, row, "", normalFormat);
//				ws.addCell(flightNoLabel);
//				Label flightNoLabel2 = new Label(1, ++row, flightNo, normalFormat);
//				ws.addCell(flightNoLabel2);	
//				
//			}else{
//				Label flightNoLabel = new Label(1, row, flightNo, normalFormat);
//				ws.addCell(flightNoLabel);		
//				
//			}
//			row++;
//		
//			
//		}
//		
//		
//		//暂时填充空格
//		for (int i = 2; i < 18; i++) {
//			for (int j = 20; j < 44; j++) {
//				Label flightNoLabel = new Label(i, j, "", normalFormat);
//				ws.addCell(flightNoLabel);
//			}	
//		}
//		
//		
//		Label flightNoLabel = new Label(2,20, "舱位组合/上座率", normalFormat);
//		ws.addCell(flightNoLabel);
		
		return ws;
	}

	private WritableSheet setHeadFirst(WritableSheet ws) throws Exception {
		this.formatWriteable();
		Label fenfangxiangLabel = new Label(0, 1, "分方向", titleFormat);
		ws.addCell(fenfangxiangLabel);
		ws.setColumnView(0, 20);
		Label hangxianLabel = new Label(1, 1, "航线", titleFormat);
		ws.addCell(hangxianLabel);
		ws.setColumnView(1, 20);
		Label flightNoLabel = new Label(2, 1, "航班号", titleFormat);
		ws.addCell(flightNoLabel);
		ws.setColumnView(2, 20);

		for (int i = 3; i < 28; i++) {

			if (i == 3) {
				Label date7Label = new Label(i, 1, "+7" + "(" + date_sheet1_7.substring(4, 6)+"."+date_sheet1_7.substring(6, 8)
						+ ")", titleFormat);
				ws.addCell(date7Label);
			} else if (i == 8) {
				Label date7Label = new Label(i, 1, "+30" + "(" + date_sheet1_30.substring(4, 6)+"."+date_sheet1_30.substring(6, 8)
						+ ")", titleFormat);
				ws.addCell(date7Label);
			} else if (i == 13) {
				Label date7Label = new Label(i, 1, "+45" + "(" + date_sheet1_45.substring(4, 6)+"."+date_sheet1_45.substring(6, 8)
						+ ")", titleFormat);
				ws.addCell(date7Label);
			} else if (i == 18) {
				Label date7Label = new Label(i, 1, "+60" + "(" + date_sheet1_60.substring(4, 6)+"."+date_sheet1_60.substring(6, 8)
						+ ")", titleFormat);
				ws.addCell(date7Label);
			} else if (i == 23) {
				Label date7Label = new Label(i, 1, "+90" + "(" + date_sheet1_90.substring(4, 6)+"."+date_sheet1_90.substring(6, 8)
						+ ")", titleFormat);
				ws.addCell(date7Label);
			}

			Label b2cLabel = new Label(i, 2, "B2C", titleFormat);
			ws.addCell(b2cLabel);
			ws.setColumnView(i, 15);
//			Label ccLabel = new Label(++i, 2, "CC", titleFormat);
//			ws.addCell(ccLabel);
//			ws.setColumnView(i, 15);
//			Label sale_cc_7 = new Label(++i, 2, "直销对比", titleFormat);
//			ws.addCell(sale_cc_7);
//			ws.setColumnView(i, 15);
			Label yilongLabel = new Label(++i, 2, "艺龙", titleFormat);
			ws.addCell(yilongLabel);
			ws.setColumnView(i, 12);
			Label sale_yilong_7 = new Label(++i, 2 ,"对比直销", titleFormat);
			ws.addCell(sale_yilong_7);
			ws.setColumnView(i, 15);
			Label xiechengLabel = new Label(++i, 2, "携程", titleFormat);
			ws.addCell(xiechengLabel);
			ws.setColumnView(i, 15);
			Label sale_xiecheng_7 = new Label(++i, 2, "对比直销", titleFormat);
			ws.addCell(sale_xiecheng_7);
			ws.setColumnView(i, 15);
//			Label yiqifeiLabel = new Label(++i, 2, "一起飞", titleFormat);
//			ws.addCell(yiqifeiLabel);
//			ws.setColumnView(i, 15);
//			Label sale_yiqifei_7 = new Label(++i, 2, "对比直销", titleFormat);
//			ws.addCell(sale_yiqifei_7);
//			ws.setColumnView(i, 15);

		}
		
		ws.mergeCells(0,0, 1, 0);
		ws.mergeCells(0, 1, 0, 2);
		ws.mergeCells(1, 1, 1, 2);
		ws.mergeCells(2, 1, 2,2);
		ws.mergeCells(3, 1, 7, 1);
		ws.mergeCells(8, 1, 12, 1);
		ws.mergeCells(13, 1, 17, 1);
		ws.mergeCells(18, 1, 22, 1);
		ws.mergeCells(23, 1, 27, 1);
		return ws;
	}

	private void initDate() {
		// 初始化日期
		currentDate = DateUtils.getDateStr("yyyy-MM-dd");

		date_sheet1_7 = DateUtils.getDateAfter(7).replaceAll("-", "");
		date_sheet1_30 = DateUtils.getDateAfter(30).replaceAll("-", "");
		date_sheet1_45 = DateUtils.getDateAfter(45).replaceAll("-", "");
		date_sheet1_60 = DateUtils.getDateAfter(60).replaceAll("-", "");
		date_sheet1_90 = DateUtils.getDateAfter(90).replaceAll("-", "");
		sheet1ListDate.clear();
		sheet1ListDate.add(date_sheet1_7);
		sheet1ListDate.add(date_sheet1_30);
		sheet1ListDate.add(date_sheet1_45);
		sheet1ListDate.add(date_sheet1_60);
		sheet1ListDate.add(date_sheet1_90);
		//sheet1ListDate.add(currentDate);
		date_sheet2_7 = DateUtils.getDateAfter(7).replaceAll("-", "");
		date_sheet2_14 = DateUtils.getDateAfter(14).replaceAll("-", "");
		date_sheet2_30 = DateUtils.getDateAfter(30).replaceAll("-", "");
		sheet2ListDate.clear();
		sheet2ListDate.add(date_sheet2_7);
		sheet2ListDate.add(date_sheet2_14);
		sheet2ListDate.add(date_sheet2_30);
		//sheet2ListDate.add(currentDate);
	}



	private File createFile(String filePath, String fileName)
			throws IOException {
		String excelpath = filePath;
		System.out.println("输出路径：" + excelpath);
		// 创建文件
		File path = new File(excelpath);
		if (path.exists()) {
			path.delete();
			path.createNewFile();
		} else {
			path.mkdirs();
		}
		//File file = new File(filePath + File.separator + fileName);
		File file = new File(filePath + fileName);
		file.createNewFile();
		return file;

	}

	public static void main(String[] args) {
		String first =  "2001-01-01 10:01";
    	String second = "2001-01-01 20:01";
		System.out.println(first.compareTo(second));
		/*try {
			new ExportOtaTemplateQuartzJob().execute();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
	}

	@Override
	public Serializable getId() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isNew() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean existed() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean notExisted() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean existed(String propertyName, Object propertyValue) {
		// TODO Auto-generated method stub
		return false;
	}

	
	
}
